<?php

  function getPageRatingBulk($urlString, $paramsString, $user_name) {
    $return_string = "";
    $urls = explode("|||", $urlString);
    $params = explode("|||", $paramsString);
    $i = 0;
    
    $url_sql_string = "("; 
    $params_sql_string = "(";
    for ($i = 0; $i < count($urls); $i++) {
      $url_sql_string .= "'" . $urls[$i] . "', ";
      $params_sql_string .= "'" . $params[$i] . "', ";
    }
    $url_sql_string = substr($url_sql_string, 0, strlen($url_sql_string) - 2); $url_sql_string .= ")";
    $params_sql_string = substr($params_sql_string, 0, strlen($params_sql_string) - 2); $params_sql_string .= ")";
    
    $user_id = -1;
    if (isset($_SESSION['user_id'])) {
      $user_id = $_SESSION['user_id'];
    }
    
    
    $query = "SELECT tab_1.link, tab_1.avg_rating, IFNULL(user_rating, 0) AS user_rating "
           . "FROM "
           . "  (SELECT CONCAT(wp.url, wpq.query) as link, (ROUND(AVG(wpr.rating),2)) AS avg_rating "
           . "   FROM "
           . "     (SELECT id, url FROM WebPage WHERE url IN ".$url_sql_string.") wp, "
           . "     (SELECT * FROM WebPageQuery WHERE query IN ".$params_sql_string.") wpq, "
           . "     WebPageRating wpr "
           . "   WHERE wp.id = wpq.webpage_id "
           . "   AND   wpq.id = wpr.webpagequery_id "
           . "   GROUP BY CONCAT(wp.url, wpq.query) "
           . "  ) tab_1 LEFT JOIN "
           . "  (SELECT CONCAT(wp.url, wpq.query) as link, (ROUND(AVG(wpr.rating),2)) AS user_rating "
           . "   FROM "
           . "     (SELECT id, url FROM WebPage WHERE url IN ".$url_sql_string.") wp, "
           . "     (SELECT * FROM WebPageQuery WHERE query IN ".$params_sql_string.") wpq, "
           . "     WebPageRating wpr "
           . "   WHERE wp.id = wpq.webpage_id "
           . "   AND   wpq.id = wpr.webpagequery_id "
           . "   AND   wpr.user_id = $user_id "
           . "   GROUP BY CONCAT(wp.url, wpq.query) "
           . "  ) tab_2 ON (tab_1.link = tab_2.link)";
    
    
    $result = mysql_query($query);
      
    while ($result_row = mysql_fetch_assoc($result)) {
      $current_link = $result_row['link'];
      $current_average_rating = $result_row['avg_rating'];
      $current_user_rating = $result_row['user_rating'];
      
      for ($i = 0; $i < count($urls); $i++) {
        $link = $urls[$i] . $params[$i];
        if ($current_link == $link){
          $return_string .= "<link num='$i'>\n";
          $return_string .= "<averagerating>$current_average_rating</averagerating>\n";
          $return_string .= "<userrating>$current_user_rating</userrating>\n";
          $return_string .= "</link>\n";
        }
      }
    }
    return $return_string;
    
    
    
    
    
      // -------------------------------------------------------------------
    
    foreach ($urls as $url) {
      $query = "SELECT id FROM WebPage WHERE url = '" . $url . "'";
      $result = mysql_query($query);
      $webpage_id = -1;
      if (mysql_num_rows($result) == 0) {
        $webpage_id = registerPage($url);
      } else {
        $result_row = mysql_fetch_assoc($result);
        $webpage_id = $result_row['id'];
      }

      $url_param = $params[$i]; 
      $webpagequery_id = getPageParameterId($webpage_id, $url_param);
      $query = "SELECT (ROUND(AVG(rating),2)) AS avgRating FROM WebPageRating"
             . " WHERE webpagequery_id=$webpagequery_id";

      $result = mysql_query($query);  
      
      $average_rating = 0;
      $result_row = mysql_fetch_assoc($result);
      if ($result_row['avgRating'])
        $average_rating = $result_row['avgRating'];
        
      if($average_rating > 0) {
        $return_string .= "<link num='$i'>\n";
        $return_string .= "<averagerating>$average_rating</averagerating>\n";

        if (isset($_SESSION['user_id'])) {
          $user_id = $_SESSION['user_id'];
          $query = "SELECT * FROM WebPageRating WHERE user_id=$user_id"
                 . " AND webpagequery_id=$webpagequery_id";
          $result = mysql_query($query);
          
          if (mysql_num_rows($result) > 0) {
            $result_row = mysql_fetch_assoc($result);
            $user_rating = $result_row['rating'];
            $return_string .= "<userrating>$user_rating</userrating>\n";
          } else {
            $return_string .= "<userrating>0</userrating>\n";
          }
        } else {
          $return_string .= "<userrating>0</userrating>\n";
        }

        $return_string .= "</link>\n";
      }
      $i++;
    }
    return $return_string;
  }
?>
